﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCMS.Model;
using System.Data.Objects.DataClasses;
using System.Transactions;
using SCMS.CoreBusinessLogic.NotificationsManager;
using System.Data;
using SCMS.Utils.DTOs;
using SCMS.CoreBusinessLogic.Budgeting;

namespace SCMS.CoreBusinessLogic.OrderRequest
{
    public class OrderRequest : IOrderRequest
    {

        private INotificationService NotificationService;
        private IBudgetService budgetService;
        private _ExchangeRate.IExchangeRateService exchangeRateService;

        public OrderRequest(_ExchangeRate.IExchangeRateService exchangeRateService, INotificationService NotificationService, IBudgetService budgetService)
        {
            this.exchangeRateService = exchangeRateService;
            this.NotificationService = NotificationService;
            this.budgetService = budgetService;
        }


        public List<OrderRequestSummary> Find(List<Guid> ids)
        {
            List<OrderRequestSummary> orders = new List<OrderRequestSummary>();

            using (var context = new SCMSEntities())
            {
                var results = from myOrders in context.OrderRequests
                              where ids.Contains(myOrders.Id)
                              select myOrders;
                //context.ExecuteStoreQuery<OrderRequestView>("SELECT * FROM SomeView").ToList();

                foreach (Model.OrderRequest item in results.ToList())
                {
                    Staff prepBy = context.Staffs.SingleOrDefault(p => p.Id == item.PreparedBy);
                    Person ps = prepBy.Person; Designation d = prepBy.Designation;
                    DateTime issueDate = (DateTime)item.PreparedOn;
                    DateTime requestDate = (DateTime)item.OrderDate;
                    var productQuery = from b in context.OrderRequestItems
                                       where b.OrderRequestId == item.Id
                                       select b;
                    String firstItem = productQuery.Count() != 0 ? productQuery.First().Item.Name : "";
                    String ProjNo = productQuery.Count() != 0 ? productQuery.First().ProjectBudget.BudgetCategory.ProjectDonor.ProjectNumber : "";
                    String ProjName = productQuery.Count() != 0 ? productQuery.First().ProjectBudget.BudgetCategory.ProjectDonor.Project.Name : "";
                    String donor = item.ProjectDonor.Donor.ShortName;

                    OrderRequestSummary tmp = new OrderRequestSummary();
                    tmp.Id = item.Id;
                    tmp.RefNumber = item.RefNumber;
                    tmp.FirstItem = firstItem;
                    tmp.ProjectNum = ProjNo;
                    tmp.ProjectName = ProjName;
                    tmp.PrepStaffNames = ps.FirstName + " " + ps.OtherNames;
                    tmp.TotalValue = item.TotalAmount.HasValue ? ((decimal)item.TotalAmount).ToString("#,###.00") : "";

                    if (item.IsAuthorized.HasValue && ((bool)item.IsAuthorized))
                    {
                        tmp.Status = "AU";
                        tmp.StatusDate = (DateTime)item.AuthorizedOn;
                    }
                    else if (item.IsRejected.HasValue && ((bool)item.IsRejected))
                    {
                        tmp.Status = "RJ";

                        if (item.AuthorizedOn.HasValue)
                        {
                            tmp.StatusDate = (DateTime)item.AuthorizedOn;
                        }
                        else
                            if (item.ReviewedOn.HasValue)
                            {
                                tmp.StatusDate = (DateTime)item.ReviewedOn;
                            }
                            else
                            {
                                tmp.StatusDate = (DateTime)item.ApprovedOn;
                            }

                    }
                    else if (item.IsReviewed.HasValue && ((bool)item.IsReviewed))
                    {
                        tmp.Status = "RV";
                        tmp.StatusDate = (DateTime)item.ReviewedOn;
                    }
                    else if (item.IsApproved)
                    {
                        tmp.Status = "AP";
                        tmp.StatusDate = (DateTime)item.ApprovedOn;
                    }
                    else if (item.IsSubmitted)
                    {
                        tmp.Status = "CR";
                        tmp.StatusDate = (DateTime)item.PreparedOn;
                    }
                    else
                    {
                        tmp.Status = "NEW";
                        tmp.StatusDate = (DateTime)item.PreparedOn;
                    }

                    orders.Add(tmp);
                }

            }

            return orders;

        }

        public bool UpdateOrderRequest(Model.OrderRequest entity)
        {

            using (var dbContext = new SCMSEntities())
            {
                dbContext.OrderRequests.Attach(entity);
                dbContext.ObjectStateManager.ChangeObjectState(entity, System.Data.EntityState.Modified);
                return (dbContext.SaveChanges() > 0) ? true : false;
            }

        }

        public bool UpdateORWithPossibleProjectChange(Model.OrderRequest or, bool projectChanged)
        {
            if (!projectChanged)
                return this.UpdateOrderRequest(or);
            using (var context = new SCMSEntities())
            {
                context.OrderRequests.Attach(or);
                var defaultBLId = context.ProjectDonors.FirstOrDefault(p => p.Id == or.ProjectDonorId).BudgetCategories.FirstOrDefault().ProjectBudgets.FirstOrDefault();
                if (defaultBLId == null)
                    return false;
                foreach (var orItem in or.OrderRequestItems)
                {
                    orItem.BudgetLineId = defaultBLId.Id;
                }
                context.ObjectStateManager.ChangeObjectState(or, EntityState.Modified);
                return context.SaveChanges() > 0;
            }
        }

        public bool DeleteOrderRequst(Guid id)
        {
            using (var dbContext = new SCMSEntities())
            {
                Model.OrderRequest odaRequest = dbContext.OrderRequests.SingleOrDefault(c => c.Id.Equals(id));
                var ppItems = dbContext.ProcurementPlanItems.Where(p => p.OrderRequestItem.OrderRequestId == id).ToList();
                foreach (var ppItem in ppItems)
                {
                    ppItem.OrderRequestItemId = null;
                    ppItem.AddedToOR = false;
                }
                dbContext.OrderRequests.DeleteObject(odaRequest);
                return (dbContext.SaveChanges() > 0) ? true : false;
            }
        }

        ///// <summary>
        ///// Updates an instance of the specified type.
        ///// </summary>
        ///// <param name="item">The instance of the given type to add.</param>
        ///// <typeparam name="T">The type of entity for which to provide the method.</typeparam>
        //public void Update<T>(T item) where T : class, new()
        //{
        //    using (var dbContext = new SCMSEntities())
        //    {
        //        dbContext.AttachTo(this.GetSetName<T>(), item);
        //        dbContext.ObjectStateManager.ChangeObjectState(item, System.Data.EntityState.Modified);
        //    }
        //}

        public void NotifiyAuthorizer(Model.OrderRequest or)
        {
            string msgBody = string.Format(NotificationHelper.orAuthMsgBody, or.RefNumber);
            NotificationService.SendNotification(NotificationService.GetApproverEmailAddress(1, NotificationHelper.orCode, NotificationHelper.authorizationCode), msgBody, NotificationHelper.orsubject);
        }

        public void NotifiyPreparer(Model.OrderRequest or)
        {

        }

        public bool AddOrderRequstItem(Model.OrderRequestItem entity, Model.OrderRequest ORentity)
        {
            using (var dbContext = new SCMSEntities())
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    try
                    {
                        var orderRequest = dbContext.OrderRequests.FirstOrDefault(o => o.Id == entity.OrderRequestId);
                        if (ORentity != null && orderRequest == null)
                        {
                            ORentity.TotalAmount = entity.EstimatedPrice;
                            dbContext.OrderRequests.AddObject(ORentity);
                            //if (dbContext.SaveChanges() > 0)
                            //{
                            entity.Id = Guid.NewGuid();
                            dbContext.OrderRequestItems.AddObject(entity);
                            if ((dbContext.SaveChanges() > 0))
                            {
                                scope.Complete();
                                return true;
                            }
                            else { scope.Dispose(); return false; }
                            //}
                            //else { scope.Dispose(); return false; }
                        }
                        else
                        {
                            if (entity.Id.Equals(Guid.Empty))
                            {

                                entity.Id = Guid.NewGuid();
                                dbContext.OrderRequestItems.AddObject(entity);
                                if ((dbContext.SaveChanges() > 0))
                                {
                                    Model.OrderRequest or = dbContext.OrderRequests.First(p => p.Id == entity.OrderRequestId);
                                    or.TotalAmount += entity.EstimatedPrice;
                                    //dbContext.OrderRequests.Attach(or);
                                    dbContext.ObjectStateManager.ChangeObjectState(or, System.Data.EntityState.Modified);
                                    if ((dbContext.SaveChanges() > 0)) { scope.Complete(); return true; } else { scope.Dispose(); return false; }
                                }
                                else { scope.Dispose(); return false; }
                            }
                            else
                            {
                                Model.OrderRequest or = dbContext.OrderRequests.First(p => p.Id == entity.OrderRequestId);
                                var orItem = dbContext.OrderRequestItems.FirstOrDefault(p => p.Id == entity.Id);
                                or.TotalAmount -= orItem.EstimatedPrice;
                                or.TotalAmount += entity.EstimatedPrice;

                                orItem.BudgetLineId = entity.BudgetLineId;
                                orItem.EstimatedPrice = entity.EstimatedPrice;
                                orItem.EstimatedUnitPrice = entity.EstimatedUnitPrice;
                                orItem.ItemDescription = entity.ItemDescription;
                                orItem.ItemId = entity.ItemId;
                                orItem.Quantity = entity.Quantity;
                                orItem.Remarks = entity.Remarks;

                                if ((dbContext.SaveChanges() > 0))
                                {
                                    scope.Complete();
                                    return true;
                                }
                                else
                                {
                                    scope.Dispose();
                                    return false;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        scope.Dispose();
                        throw new Exception(ex.Message);
                    }
                }
            }
        }

        public bool EditOrderRequestItem(Model.OrderRequestItem entity)
        {

            using (var dbContext = new SCMSEntities())
            {
                dbContext.OrderRequestItems.Attach(entity);
                dbContext.ObjectStateManager.ChangeObjectState(entity, System.Data.EntityState.Modified);
                return (dbContext.SaveChanges() > 0) ? true : false;
            }

        }

        public bool DeleteOrderRequstItem(Guid id)
        {
            using (var dbContext = new SCMSEntities())
            {
                Model.OrderRequestItem odaRequest = dbContext.OrderRequestItems.SingleOrDefault(c => c.Id.Equals(id));
                var or = dbContext.OrderRequests.SingleOrDefault(o => o.Id == odaRequest.OrderRequestId);
                or.TotalAmount -= odaRequest.EstimatedPrice;
                var ppItem = dbContext.ProcurementPlanItems.FirstOrDefault(p => p.OrderRequestItemId == id);
                if (ppItem != null)
                {
                    ppItem.OrderRequestItemId = null;
                    ppItem.AddedToOR = false;
                }
                dbContext.OrderRequestItems.DeleteObject(odaRequest);
                return (dbContext.SaveChanges() > 0) ? true : false;
            }
        }

        public string GenerateUniquNumber(CountryProgramme cp)
        {
            string code = "OR/DRC/" + cp.Country.ShortName + "/";
            string refNumber = "";
            long count = 1;
            using (var dbContext = new SCMSEntities())
            {
                var total = dbContext.OrderRequests.Where(p => p.CountryProgrammeId == cp.Id && p.IsSubmitted == true).Count();
                count = total;
                Model.OrderRequest m = null;
                do
                {
                    count++;
                    if (count < 10000)
                    {
                        if (count < 10)
                            refNumber = code + "0000" + count;
                        if (count < 100 & count >= 10)
                            refNumber = code + "000" + count;
                        if (count < 1000 & count >= 100)
                            refNumber = code + "00" + count;
                        if (count < 10000 & count >= 1000)
                            refNumber = code + "0" + count;
                    }
                    m = dbContext.OrderRequests.FirstOrDefault(p => p.RefNumber == refNumber);
                } while (m != null);
                return refNumber;
            }
        }

        public List<Model.OrderRequest> Search(Model.OrderRequest entity)
        {
            using (var dbContext = new SCMSEntities())
            {

                List<Model.OrderRequest> orlist = dbContext.OrderRequests.Where(p => p.RefNumber.Contains(entity.RefNumber)).OrderByDescending(p => p.PreparedOn).ToList();
                return orlist;
            }
        }

        public List<Model.OrderRequest> Search(String refNum)
        {
            using (var dbContext = new SCMSEntities())
            {

                List<Model.OrderRequest> orlist = dbContext.OrderRequests.Where(p => p.RefNumber.Contains(refNum))
                    .OrderByDescending(p => p.PreparedOn).ToList();
                return orlist;
            }
        }


        public List<Model.OrderRequest> Search(DateTime fromDate, DateTime toDate, int startIndex = 0, int pageSize = 10)
        {
            using (var dbContext = new SCMSEntities())
            {

                List<Model.OrderRequest> orlist = dbContext.OrderRequests.Where(order => order.OrderDate >= fromDate && order.OrderDate <= toDate)
                    .OrderByDescending(p => p.PreparedOn).Skip(startIndex).Take(pageSize).ToList();
                return orlist;
            }
        }

        public long SearchCount(DateTime fromDate, DateTime toDate)
        {
            using (var dbContext = new SCMSEntities())
            {
                long count = dbContext.OrderRequests.Where(order => order.OrderDate >= fromDate && order.OrderDate <= toDate).Count();
                return count;
            }
        }

        public List<Model.OrderRequest> GetOrderRequests(Guid CPId)
        {
            using (var context = new SCMSEntities())
            {
                var orList = context.OrderRequests.Where(p => p.CountryProgrammeId == CPId).OrderByDescending(p => p.PreparedOn).ToList();
                foreach (var or in orList)
                {
                    var donor = or.ProjectDonor.Donor;
                }
                return orList;
            }
        }

        public Model.OrderRequestItem GetOrderRequestItemById(Guid id)
        {
            using (var context = new SCMSEntities())
            {
                return context.OrderRequestItems.FirstOrDefault(p => p.Id == id);
            }
        }

        public List<Model.CountrySubProgramme> CountrySubProgs(Guid id)
        {
            using (var context = new SCMSEntities())
            {
                return context.CountrySubProgrammes.Where(p => p.CountryProgrammeId == id).ToList();
            }
        }

        public List<Model.BudgetLineView> GetProjectBugdetLines(Guid pdid)
        {
            try
            {
                using (var context = new SCMSEntities())
                {
                    List<BudgetLineView> blView = new List<BudgetLineView>();
                    var blList = context.ProjectBudgets.Where(p => p.BudgetCategory.ProjectDonorId == pdid).ToList<Model.ProjectBudget>();
                    blList = SCMS.Utils.BudgetLineSorter.SortBudgetLine(blList);
                    foreach (var bl in blList)
                    {
                        blView.Add(new BudgetLineView
                        {
                            Id = bl.Id,
                            Description = bl.LineNumber + " " + bl.Description
                        });
                    }
                    return blView;
                }
            }
            catch (Exception ex)
            {
                return new List<BudgetLineView>();
            }
        }

        public List<Model.OrderRequestItem> GetOrderRequestItems(Guid orderRequestid)
        {
            using (var context = new SCMSEntities())
            {
                List<Model.OrderRequestItem> ORItems = context.OrderRequestItems.Where(p => p.OrderRequestId == orderRequestid).ToList<OrderRequestItem>();
                foreach (var item in ORItems)
                {
                    Model.UnitOfMeasure u = item.Item.UnitOfMeasure;
                    Model.OrderRequest or = item.OrderRequest;
                    Item i = item.Item;
                }
                return ORItems;
            }
        }

        public List<Model.ProjectDonor> GetProjectNos(Guid CPId, Guid? projectId)
        {
            using (var context = new SCMSEntities())
            {
                List<ProjectDonor> pd; DateTime tude = DateTime.Now.Date;
                if (projectId != null)
                    pd = context.ProjectDonors.Where(p => p.Project.CountryProgrammeId == CPId && p.ProjectId == projectId && p.EndDate >= DateTime.Today).ToList();
                else pd = context.ProjectDonors.Where(p => p.Project.CountryProgrammeId == CPId && p.EndDate >= DateTime.Today).OrderBy(p => p.ProjectNumber).ToList();
                foreach (ProjectDonor item in pd)
                {
                    Donor d = item.Donor;
                }
                return pd;
            }
        }

        public List<Project> GetProject(Guid CPId)
        {
            using (var db = new SCMSEntities())
            {
                return db.Projects.Where(p => p.CountryProgrammeId == CPId && p.ProjectDonors.Where(pd => pd.EndDate >= DateTime.Today).Count() > 0).OrderBy(p => p.Name).ToList();
            }
        }

        public List<Project> GetProjectsWithPP(Guid CPId)
        {
            using (var db = new SCMSEntities())
            {
                return db.Projects.Where(p => p.CountryProgrammeId == CPId && p.ProjectDonors.Where(r => r.EndDate >= DateTime.Today && r.ProcurementPlans.Count > 0).Count() > 0).OrderBy(p => p.Name).ToList();
            }
        }

        public List<ProjectDonor> GetProjectNosWithPP(Guid CPId, Guid? projectId = null)
        {
            using (var context = new SCMSEntities())
            {
                List<ProjectDonor> pd; DateTime tude = DateTime.Now.Date;
                if (projectId != null)
                    pd = context.ProjectDonors.Where(p => p.Project.CountryProgrammeId == CPId && p.ProjectId == projectId && p.ProcurementPlans.Count > 0).ToList();
                else pd = context.ProjectDonors.Where(p => p.Project.CountryProgrammeId == CPId && p.ProcurementPlans.Count > 0).OrderBy(p => p.ProjectNumber).ToList();
                foreach (ProjectDonor item in pd)
                {
                    Donor d = item.Donor;
                }
                return pd;
            }
        }

        public Model.ProjectDonor GetProjectDonorDetails(Guid id)
        {
            using (var context = new SCMSEntities())
            {
                return context.ProjectDonors.FirstOrDefault(p => p.Id == id);
            }
        }

        public Model.ProjectBudget GetProjectBudgetDetails(Guid id)
        {
            using (var context = new SCMSEntities())
            {
                return context.ProjectBudgets.FirstOrDefault(p => p.Id == id);
            }
        }

        public List<Model.UnitOfMeasure> GetUnitMesures(Guid id)
        {
            using (var context = new SCMSEntities())
            {
                return context.UnitOfMeasures.Where(p => p.CountryProgrammeId == id).ToList();
            }
        }

        public List<Model.Currency> GetCurrencies(Guid id)
        {
            using (var context = new SCMSEntities())
            {
                return context.Currencies.Where(p => p.CountryProgrammeId == id).OrderBy(c => c.ShortName).ToList();
            }

        }

        public List<Model.Location> GetLocations(Guid id)
        {
            using (var context = new SCMSEntities())
            {
                return context.Locations.Where(l => l.CountryProgrammeId == id).OrderBy(l => l.Name).ToList();
            }

        }

        public List<Model.Item> GetItems(Guid CPId, string category = null)
        {
            using (var context = new SCMSEntities())
            {
                switch (category)
                {
                    case "C":
                        return context.Items.Where(p => p.CountryProgrammeId == CPId & p.ItemCategory.CategoryCode.StartsWith("C") == true).OrderBy(i => i.Name).ToList();
                    case "A":
                        return context.Items.Where(p => p.CountryProgrammeId == CPId & p.ItemCategory.CategoryCode.StartsWith("A") == true).OrderBy(i => i.Name).ToList();
                    default:
                        return context.Items.Where(p => p.CountryProgrammeId == CPId).OrderBy(i => i.Name).ToList();
                }
            }
        }

        public List<Model.OrderRequest> GetOrderRequestsForApproval(SystemUser currentUser)
        {
            try
            {
                using (var context = new SCMSEntities())
                {
                    List<Model.OrderRequest> orderRequests = new List<Model.OrderRequest>();
                    context.SystemUsers.Attach(currentUser);
                    var approvers = context.Approvers.Where(a => a.UserId == currentUser.Id && a.ActivityCode == NotificationHelper.orCode && a.ActionType == NotificationHelper.approvalCode).ToList();
                    var delegateApprovers = context.Approvers.Where(a => a.AssistantId == currentUser.Id && a.ActivityCode == NotificationHelper.orCode && a.ActionType == NotificationHelper.approvalCode).ToList();
                    if (approvers != null)
                    {
                        foreach (var approver in approvers)
                        {
                            var orList = context.OrderRequests.Where(o => o.CountryProgrammeId == currentUser.Staff.CountrySubOffice.CountryProgrammeId && (o.IsApproved == false && o.IsSubmitted == true && o.IsRejected == false)
                                && o.Notifications.Where(n => (Guid)n.OrderRequestId == o.Id && n.IsRespondedTo == false && n.SentToDelegate == false && n.ApproverId == approver.Id).Count() > 0).ToList();
                            foreach (var or in orList)
                            {
                                var person = or.Staff2.Person.FirstName;
                                foreach (var orItem in or.OrderRequestItems.ToList())
                                {
                                    var item = orItem.Item;
                                    var pd = orItem.ProjectBudget.BudgetCategory.ProjectDonor;
                                    var pj = orItem.ProjectBudget.BudgetCategory.ProjectDonor.Project;
                                }
                                orderRequests.Add(or);
                            }
                        }
                    }
                    if (delegateApprovers != null)
                    {
                        foreach (var delegateApprover in delegateApprovers)
                        {
                            var orList = context.OrderRequests.Where(o => o.CountryProgrammeId == currentUser.Staff.CountrySubOffice.CountryProgrammeId && (o.IsApproved == false && o.IsSubmitted == true && o.IsRejected == false)
                                && o.Notifications.Where(n => (Guid)n.OrderRequestId == o.Id && n.IsRespondedTo == false && n.SentToDelegate == true && n.ApproverId == delegateApprover.Id).Count() > 0).ToList();
                            foreach (var or in orList)
                            {
                                var person = or.Staff2.Person.FirstName;
                                foreach (var orItem in or.OrderRequestItems.ToList())
                                {
                                    var item = orItem.Item;
                                    var pd = orItem.ProjectBudget.BudgetCategory.ProjectDonor;
                                    var pj = orItem.ProjectBudget.BudgetCategory.ProjectDonor.Project;
                                }
                                orderRequests.Add(or);
                            }
                        }
                    }
                    return orderRequests;
                }
            }
            catch (Exception ex)
            {
                return new List<Model.OrderRequest>();
            }
        }

        public List<Model.OrderRequest> GetOrderRequestsForReview(SystemUser currentUser)
        {
            try
            {
                using (var context = new SCMSEntities())
                {
                    List<Model.OrderRequest> orderRequests = new List<Model.OrderRequest>();
                    context.SystemUsers.Attach(currentUser);
                    var approvers = context.Approvers.Where(a => a.UserId == currentUser.Id && a.ActivityCode == NotificationHelper.orCode && a.ActionType == NotificationHelper.reviewCode).ToList();
                    var delegateApprovers = context.Approvers.Where(a => a.AssistantId == currentUser.Id && a.ActivityCode == NotificationHelper.orCode && a.ActionType == NotificationHelper.reviewCode).ToList();
                    if (approvers != null)
                    {
                        foreach (var approver in approvers)
                        {
                            var orList = context.OrderRequests.Where(o => o.CountryProgrammeId == currentUser.Staff.CountrySubOffice.CountryProgrammeId && (o.IsReviewed == false && o.IsApproved == true && o.IsRejected == false)
                                && o.Notifications.Where(n => (Guid)n.OrderRequestId == o.Id && n.IsRespondedTo == false && n.SentToDelegate == false && n.ApproverId == approver.Id).Count() > 0).ToList();
                            foreach (var or in orList)
                            {
                                var person = or.Staff2.Person.FirstName;
                                foreach (var orItem in or.OrderRequestItems.ToList())
                                {
                                    var item = orItem.Item;
                                    var pd = orItem.ProjectBudget.BudgetCategory.ProjectDonor;
                                    var pj = orItem.ProjectBudget.BudgetCategory.ProjectDonor.Project;
                                }
                                orderRequests.Add(or);
                            }
                        }
                    }
                    if (delegateApprovers != null)
                    {
                        foreach (var delegateApprover in delegateApprovers)
                        {
                            var orList = context.OrderRequests.Where(o => o.CountryProgrammeId == currentUser.Staff.CountrySubOffice.CountryProgrammeId && (o.IsReviewed == false && o.IsApproved == true && o.IsRejected == false)
                                && o.Notifications.Where(n => (Guid)n.OrderRequestId == o.Id && n.IsRespondedTo == false && n.SentToDelegate == true && n.ApproverId == delegateApprover.Id).Count() > 0).ToList();
                            foreach (var or in orList)
                            {
                                var person = or.Staff2.Person.FirstName;
                                foreach (var orItem in or.OrderRequestItems.ToList())
                                {
                                    var item = orItem.Item;
                                    var pd = orItem.ProjectBudget.BudgetCategory.ProjectDonor;
                                    var pj = orItem.ProjectBudget.BudgetCategory.ProjectDonor.Project;
                                }
                                orderRequests.Add(or);
                            }
                        }
                    }
                    return orderRequests;
                }
            }
            catch (Exception ex)
            {
                return new List<Model.OrderRequest>();
            }
        }

        public List<Model.OrderRequest> GetOrderRequestsForAuth(SystemUser currentUser)
        {
            try
            {
                using (var context = new SCMSEntities())
                {
                    List<Model.OrderRequest> orderRequests = new List<Model.OrderRequest>();
                    context.SystemUsers.Attach(currentUser);
                    var approvers = context.Approvers.Where(a => a.UserId == currentUser.Id && a.ActivityCode == NotificationHelper.orCode && a.ActionType == NotificationHelper.authorizationCode).ToList();
                    var delegateApprovers = context.Approvers.Where(a => a.AssistantId == currentUser.Id && a.ActivityCode == NotificationHelper.orCode && a.ActionType == NotificationHelper.authorizationCode).ToList();
                    if (approvers != null)
                    {
                        foreach (var approver in approvers)
                        {
                            var orList = context.OrderRequests.Where(o => o.CountryProgrammeId == currentUser.Staff.CountrySubOffice.CountryProgrammeId && (o.IsReviewed == true && o.IsRejected == false && o.IsAuthorized == false)
                                && o.Notifications.Where(n => (Guid)n.OrderRequestId == o.Id && n.IsRespondedTo == false && n.SentToDelegate == false && n.ApproverId == approver.Id).Count() > 0).ToList();
                            foreach (var or in orList)
                            {
                                var person = or.Staff2.Person.FirstName;
                                foreach (var orItem in or.OrderRequestItems.ToList())
                                {
                                    var item = orItem.Item;
                                    var pd = orItem.ProjectBudget.BudgetCategory.ProjectDonor;
                                    var pj = orItem.ProjectBudget.BudgetCategory.ProjectDonor.Project;
                                }
                                orderRequests.Add(or);
                            }
                        }
                    }
                    if (delegateApprovers != null)
                    {
                        foreach (var delegateApprover in delegateApprovers)
                        {
                            var orList = context.OrderRequests.Where(o => o.CountryProgrammeId == currentUser.Staff.CountrySubOffice.CountryProgrammeId && (o.IsReviewed == true && o.IsRejected == false && o.IsAuthorized == false)
                                && o.Notifications.Where(n => (Guid)n.OrderRequestId == o.Id && n.IsRespondedTo == false && n.SentToDelegate == true && n.ApproverId == delegateApprover.Id).Count() > 0).ToList();
                            foreach (var or in orList)
                            {
                                var person = or.Staff2.Person.FirstName;
                                foreach (var orItem in or.OrderRequestItems.ToList())
                                {
                                    var item = orItem.Item;
                                    var pd = orItem.ProjectBudget.BudgetCategory.ProjectDonor;
                                    var pj = orItem.ProjectBudget.BudgetCategory.ProjectDonor.Project;
                                }
                                orderRequests.Add(or);
                            }
                        }
                    }
                    return orderRequests;
                }
            }
            catch (Exception ex)
            {
                return new List<Model.OrderRequest>();
            }
        }

        /// <summary>
        /// Use raw SQL for speed and custom pagination
        /// </summary>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public OrderRequestPagePacket getPagedOrderRequests(int page = 1, int size = 25, Dictionary<String, String> args = null)
        {
            // Raw sql
            string qString = @"SELECT * FROM (SELECT oq.Id, RefNumber, TotalAmount AS ORValue, pd.ProjectNumber,
                                CASE 
	                                WHEN oq.IsAuthorized=1 THEN 'AU'
	                                WHEN oq.IsRejected=1 THEN 'RJ'
	                                WHEN oq.IsReviewed=1 THEN 'RV'
	                                WHEN oq.IsApproved=1 THEN 'AP'
	                                WHEN oq.IsSubmitted=1 THEN 'CR'
	                                ELSE 'NEW' END 
                                AS Status, 
                                CASE 
	                                WHEN oq.IsAuthorized=1 THEN AuthorizedOn
	                                WHEN oq.IsRejected=1 AND oq.AuthorizedOn IS NOT NULL THEN oq.AuthorizedOn
	                                WHEN oq.IsRejected=1 AND oq.ReviewedOn IS NOT NULL THEN oq.ReviewedOn
	                                WHEN oq.IsRejected=1 AND oq.ApprovedOn IS NOT NULL THEN oq.ApprovedOn
	                                WHEN oq.IsReviewed=1 THEN oq.ReviewedOn
	                                WHEN oq.IsApproved=1 THEN oq.ApprovedOn
	                                WHEN oq.IsSubmitted=1 THEN oq.PreparedOn
	                                ELSE oq.PreparedOn END 
                                AS StatusDate,
                                pn.FirstName+' '+ pn.OtherNames AS Requestor,
                                ri2.Name AS FirstItem,
                                oq.CountryProgrammeId,
                                ROW_NUMBER() OVER(ORDER BY oq.RefNumber) AS RowNum
                                FROM orderRequest oq
                                JOIN projectDonor pd ON oq.ProjectDonorId=pd.Id 
                                JOIN Staff sf ON sf.id= oq.PreparedBy
                                JOIN Person pn ON pn.Id = sf.PersonId
                                CROSS APPLY (select top(1) ri.ItemId, ri.Id,ri.OrderRequestId, im.Name from orderrequestitem ri JOIN Item im ON im.Id=ri.ItemId WHERE ri.OrderRequestId=oq.Id) ri2 
                                ) orders
                                WHERE RowNum>({PAGE_NUM}-1)*{PAGE_SIZE} AND RowNum<={PAGE_NUM}*{PAGE_SIZE}  {PREDICATE_QUERY}";

            String totalQuery = "SELECT COUNT(*) AS TotalCount FROM OrderRequest {PREDICATE_TOTAL}";

            int _page = page;

            if (_page < 1)
            {
                _page = 1;
            }

            int _size = 10;

            if (size > 10)
            {
                _size = size;
            }


            qString = qString.Replace("{PAGE_SIZE}", _size.ToString());

            Dictionary<String, String> options = new Dictionary<string, string>();

            if (args != null)
            {
                options = args;
            }

            if (!options.ContainsKey("CountryProgrammeId"))
            {
                //force
            }


            totalQuery = totalQuery.Replace("{PREDICATE_TOTAL}", "WHERE CountryProgrammeId='" + options["CountryProgrammeId"] + "'");

            String queryPredicate = " AND CountryProgrammeId='" + options["CountryProgrammeId"] + "'";

            qString = qString.Replace("{PREDICATE_QUERY}", queryPredicate);

            OrderRequestPagePacket pack = new OrderRequestPagePacket();

            using (var context = new SCMSEntities())
            {
                pack.TotalOrders = context.ExecuteStoreQuery<Int32>(totalQuery).First<Int32>();

                if ((pack.TotalOrders - _page * _size) <= _size)
                {
                    _page++;
                }

                qString = qString.Replace("{PAGE_NUM}", _page.ToString());

                pack.Orders = context.ExecuteStoreQuery<OrderRequestSummary>(qString).ToList<OrderRequestSummary>();
            }

            return pack;
        }

        public Model.OrderRequest GetOrderRequestById(Guid id)
        {
            try
            {
                using (var context = new SCMSEntities())
                {
                    var or = context.OrderRequests.FirstOrDefault(o => o.Id == id);
                    var pd = or.ProjectDonor.Donor;
                    var currency = or.Currency;
                    if (or.Staff != null)
                    {
                        var person = or.Staff.Person;
                        var desg = or.Staff.Designation;
                    }
                    if (or.Staff1 != null)
                    {
                        var person = or.Staff1.Person;
                        var desg = or.Staff1.Designation;
                    }
                    if (or.Staff2 != null)
                    {
                        var person = or.Staff2.Person;
                        var desg = or.Staff2.Designation;
                    }
                    if (or.Staff3 != null)
                    {
                        var person = or.Staff3.Person;
                        var desg = or.Staff3.Designation;
                    }
                    return or;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public void AuthorizeOrderRequest(Model.OrderRequest or)
        {
            try
            {
                decimal? amount;
                decimal? currAmount;//Amount in ProjectBudget currency
                using (var context = new SCMSEntities())
                {
                    var orItems = context.OrderRequestItems.Where(i => i.OrderRequestId == or.Id).ToList();
                    foreach (var orItem in orItems)
                    {
                        amount = orItem.EstimatedPrice;
                        currAmount = exchangeRateService.GetForeignCurrencyValue(orItem.ProjectBudget.BudgetCategory.ProjectDonor.Currency, orItem.OrderRequest.Currency, amount, (Guid)or.CountryProgrammeId);
                        orItem.ProjectBudget.TotalCommitted += currAmount;

                        //Add to BudgetCommitment
                        var budgetCommitment = new BudgetCommitment();
                        budgetCommitment.Id = Guid.NewGuid();
                        budgetCommitment.OrderRequestItemId = orItem.Id;
                        budgetCommitment.AmountCommitted = (decimal)currAmount;
                        budgetCommitment.DateCommitted = DateTime.Now;
                        budgetCommitment.BudgetLineId = orItem.ProjectBudget.Id;
                        context.BudgetCommitments.AddObject(budgetCommitment);
                    }
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

            }
        }

        public void UndoAuthorization(Model.OrderRequest or)
        {
            try
            {
                decimal? amount;
                decimal? currAmount;//Amount in ProjectBudget currency
                using (var context = new SCMSEntities())
                {
                    var orItems = context.OrderRequestItems.Where(i => i.OrderRequestId == or.Id).ToList();
                    foreach (var orItem in orItems)
                    {
                        amount = orItem.EstimatedPrice;
                        currAmount = exchangeRateService.GetForeignCurrencyValue(orItem.ProjectBudget.BudgetCategory.ProjectDonor.Currency, orItem.OrderRequest.Currency, amount, (Guid)or.CountryProgrammeId);
                        orItem.ProjectBudget.TotalCommitted -= currAmount;

                        var budgetCommitmentList = context.BudgetCommitments.Where(b => b.OrderRequestItemId == orItem.Id).ToList();

                        foreach (var budgetCommitment in budgetCommitmentList)
                        {
                            context.BudgetCommitments.DeleteObject(budgetCommitment);
                        }
                    }
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

            }
        }

        public float GetLastPOItemPrice(Guid itemId)
        {
            using (var db = new SCMSEntities())
            {
                double quary = (from orit in db.OrderRequestItems
                             join poit in db.PurchaseOrderItems on orit.Id equals poit.OrderRequestItemId
                             join po in db.PurchaseOrders on poit.PurchaseOrderId equals po.Id
                             where orit.ItemId == itemId
                             orderby po.PreparedOn descending
                             select poit.UnitPrice).FirstOrDefault();
                return (float)((0.1 * quary) + quary);

            }
        }

        public void BackDateOR(Model.OrderRequest or)
        {
            using (var context = new SCMSEntities())
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    try
                    {
                        //Get Current OR from DB
                        var currentOR = context.OrderRequests.FirstOrDefault(o => o.Id == or.Id);
                        //Initialize BackDate object
                        var backDate = new DocumentBackDating();
                        backDate.Id = Guid.NewGuid();
                        backDate.BackDatedBy = or.BackDatedBy;
                        backDate.BackDatedOn = DateTime.Now;
                        backDate.NewDate = or.OrderDate.Value;
                        backDate.OrderRequestId = or.Id;
                        backDate.PreviousDate = currentOR.OrderDate.Value;
                        backDate.Reason = or.BackDatingReason;
                        //Insert BackDate details into db
                        context.DocumentBackDatings.AddObject(backDate);
                        //Update date on OR in db
                        currentOR.OrderDate = or.OrderDate;
                        context.SaveChanges();
                        scope.Complete();
                    }
                    catch (Exception ex)
                    {
                        scope.Dispose();
                        throw ex;
                    }
                }
            }
        }

        public List<BudgetCheckResult> RunFundsAvailableCheck(Guid orId)
        {
            var bcrList = new List<BudgetCheckResult>();
            using (var context = new SCMSEntities())
            {
                var orItems = context.OrderRequestItems.IncludeOrderRequest().IncludeProjectDonor().Where(o => o.OrderRequestId == orId);
                var or = orItems.ToList()[0].OrderRequest;
                //Construct list of project budgets for that OR
                List<ProjectBudget> pbList = new List<ProjectBudget>();
                foreach (var orItem in orItems.ToList())
                {
                    if (!pbList.Contains(orItem.ProjectBudget))
                        pbList.Add(orItem.ProjectBudget);
                }
                foreach (var pb in pbList)
                {
                    decimal totalAmount = orItems.Where(b => b.BudgetLineId == pb.Id).Sum(b => b.EstimatedPrice);
                    if (!budgetService.SufficientFundsAvailable(totalAmount, pb.Id.ToString(), or.CurrencyId.ToString(), Guid.Empty, Guid.Empty, Guid.Empty))
                    {
                        decimal availableFunds = pb.TotalBudget - (decimal)(pb.TotalCommitted + pb.TotalPosted);
                        availableFunds = (decimal)exchangeRateService.GetForeignCurrencyValue(or.CurrencyId, pb.BudgetCategory.ProjectDonor.CurrencyId.Value, availableFunds, or.CountryProgrammeId.Value);
                        bcrList.Add(new BudgetCheckResult { ProjectBudget = pb, AmountChecked = totalAmount, AvailableAmount = availableFunds, FundsAvailable = false });
                    }
                }
            }
            return bcrList;
        }
    }
}
